/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package generator.connection;

/**
 *
 * @author Aleksandar
 */
import java.io.File;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.DatabaseMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;


public class SQLConnectionDatabase {
    
    protected String dbFolder = "db";
    protected String dbFileName = "connection.db";
    protected String dbTableName = "dbconnection";
    protected String sTempDb = dbFolder + File.separator + dbFileName;
    protected String sJdbc = "jdbc:sqlite";
    protected String sDbUrl = sJdbc + ":" + sTempDb;
    // which will produce a legitimate Url for SqlLite JDBC :
    // jdbc:sqlite:connection.db
    protected String sMakeTable = "CREATE TABLE IF NOT EXISTS " + dbTableName + " (id numeric, dbtype text, dbdriver text, active numeric, dbname text, dbuser text, dbpassword text, dbPort text, jdbcURL text)";
    protected String sMakeSelect = "SELECT * from " + dbTableName;
    protected int iTimeout = 30; // in sec
    protected Connection conn = null;

    public SQLConnectionDatabase() throws ClassNotFoundException, SQLException {
        init();
    }
    
    public void init() throws ClassNotFoundException, SQLException {
        loadDriver();
        conn = DriverManager.getConnection(sDbUrl);
        createDataBase(); 
    }
    
    protected void loadDriver() throws ClassNotFoundException{
        // register the driver 
        String sDriverName = "org.sqlite.JDBC";
        Class.forName(sDriverName);
    }
    
    public void createDataBase() throws SQLException{
        
        // create db folder if not exists
        File isdbFolder = new File("."+File.separator+dbFolder);
        if(!isdbFolder.exists())
        {
            isdbFolder.mkdir();
        }
        
        // now we set up a set of fairly basic string variables to use in the body of the code proper
        // create a database connection
        //Connection conn = DriverManager.getConnection(sDbUrl);
        //try {
            Statement stmt = conn.createStatement();
            try {
                stmt.setQueryTimeout(iTimeout);
                stmt.executeUpdate(sMakeTable);
            } finally {
                try {
                    stmt.close();
                } catch (Exception ignore) {
                }
            }
        //}
//        finally {
//            try {
//                conn.close();
//            } catch (Exception ignore) {
//            }
//        }
    }
    
    public synchronized List<DataBaseConnectionModel> readFromDataBase() throws SQLException{
        // now we set up a set of fairly basic string variables to use in the body of the code proper
        // create a database connection
        List<DataBaseConnectionModel> dbList = new ArrayList<DataBaseConnectionModel>();
        //Connection conn = DriverManager.getConnection(sDbUrl);
        //try {
            Statement stmt = conn.createStatement();
            try {
                stmt.setQueryTimeout(iTimeout);
                ResultSet rs = stmt.executeQuery(sMakeSelect);
                try {
                    while (rs.next()) {
                        int sResult = rs.getInt("id");
                        String sResult1 = rs.getString("dbtype");
                        String sResult2 = rs.getString("dbdriver");
                        int sResult3 = rs.getInt("active");
                        String sResult4 = rs.getString("dbname");
                        String sResult5 = rs.getString("dbuser");
                        String sResult6 = rs.getString("dbpassword");
                        String sResult7 = rs.getString("dbPort");
                        String sResult8 = rs.getString("jdbcURL");
                        //System.out.println(sResult);
                        dbList.add(new DataBaseConnectionModel(sResult, sResult1, sResult2, sResult3, sResult4, sResult5, sResult6, sResult7, sResult8));
                    }
                } finally {
                    try {
                        rs.close();
                    } catch (Exception ignore) {
                    }
                }
            } finally {
                try {
                    stmt.close();
                } catch (Exception ignore) {
                }
            }
//        } finally {
//            try {
//                conn.close();
//            } catch (Exception ignore) {
//            }
//        }
        
        return dbList;
    }
    
    public synchronized boolean insertInDataBase(DataBaseConnectionModel dbcm) throws SQLException{
        // now we set up a set of fairly basic string variables to use in the body of the code proper
        // create a database connection
        boolean isInsert = false;
        //Connection conn = DriverManager.getConnection(sDbUrl);
        //try {
            Statement stmt = conn.createStatement();
            try {
                stmt.setQueryTimeout(iTimeout);
                String sMakeInsert = "INSERT INTO " + dbTableName + " VALUES(" + dbcm.id + ",'" + dbcm.dbType + "','" + dbcm.dbDriver + "', " + dbcm.active + " , '" + dbcm.dbName + "' , '" + dbcm.dbUser + "' , '" + dbcm.dbPassword + "' , '" + dbcm.dbPort + "' , '" + dbcm.jdbcURL  + "' )";
                isInsert = stmt.executeUpdate(sMakeInsert) > 0 ? true : false;
            } finally {
                try {
                    stmt.close();
                } catch (Exception ignore) {
                }
            }
        //} 
//        finally {
//            try {
//                conn.close();
//            } catch (Exception ignore) {
//            }
//        }
        
        return isInsert;
    }
    
    public synchronized boolean updateDataBase(DataBaseConnectionModel dbcm) throws SQLException{
        // now we set up a set of fairly basic string variables to use in the body of the code proper
        // create a database connection
        boolean isUpdate = false;
        //Connection conn = DriverManager.getConnection(sDbUrl);
        //try {
            Statement stmt = conn.createStatement();
            try {
                stmt.setQueryTimeout(iTimeout);
                String sMakeUpdate = "UPDATE " + dbTableName + " SET dbtype='"+ dbcm.dbType +"' , dbdriver='" + dbcm.dbDriver + "' , active=" + dbcm.active  + " , dbName='" + dbcm.dbName + "' , dbUser='" + dbcm.dbUser + "' , dbPassword='" + dbcm.dbPassword + "' , dbPort='" + dbcm.dbPort + "' , jdbcURL='" + dbcm.jdbcURL + "' WHERE id=" + dbcm.id + ";";
                
                isUpdate = stmt.executeUpdate(sMakeUpdate) > 0 ? true : false;
            } finally {
                try {
                    stmt.close();
                } catch (Exception ignore) {
                }
            }
//        } finally {
//            try {
//                conn.close();
//            } catch (Exception ignore) {
//            }
//        }
        
        return isUpdate;
    }
    
    public void cretaData() throws Exception {
    
        insertInDataBase(new DataBaseConnectionModel(1, "MySql", "com.mysql.jdbc.Driver", 1, "test", "root", "abosancic", "3306", "jdbc:mysql://127.0.0.1:3306/test"));
        insertInDataBase(new DataBaseConnectionModel(2, "SQLite", "org.sqlite.JDBC", 0, "test", "root", "abosancic", "", "jdbc:mysql://127.0.0.1:3306/test"));
        insertInDataBase(new DataBaseConnectionModel(3, "Oracle", "oracle.jdbc.driver.OracleDriver", 0, "XE", "hr", "hr", "1521", "jdbc:oracle:thin:@localhost:1521:XE"));
        
    }
    

    public static void main(String[] args) throws Exception {
    
        
//        SQLConnectionDatabase db = new SQLConnectionDatabase();
//        
//        List<DataBaseConnectionModel> lis = db.readFromDataBase();     
//        db.cretaData();
//        lis = db.readFromDataBase();       
//        for (DataBaseConnectionModel dataBaseConnectionModel : lis) {
//            System.out.println(dataBaseConnectionModel);
//        }
        
    }
   
}